﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Data;
using MockPrjTeam2.Common;
using MockPrjTeam2.Entity;
namespace MockPrjTeam2.DAO
{
    public class DirectorateDao
    {
        //method GetDirectorateByorgId
        public DataTable GetDirectorateByorgId(int idOrg)
        {
            string query = "select Directorate.DirectorateName,DirectorateID,Directorate.AddressLine1,Directorate.IsActive,DirectorateID,PostCode,ContactName from Directorate inner join Address on Directorate.AddressID=Address.AddressID inner join Contact on Directorate.ContactID=Contact.ContactID where OrgID=@OrgID and Directorate.IsActive =1 order by Directorate.DirectorateName asc";
           SqlParameter[]sqlParameters=new SqlParameter[1];
            sqlParameters[0]=new SqlParameter("@OrgID",SqlDbType.Int);
            sqlParameters[0].Value = idOrg;
            using (var helper=new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query,sqlParameters);
            }
        }
        //method  public bool AddDirectorate(Directorate directorate)
        public bool AddDirectorate(Directorate directorate)
        {
           bool retVal = false;
            try
            {
            string query = @"insert into Directorate(DirectorateName,DirectorateShortDescription,AddressLine1,AddressLine2,AddressLine3,AddressID,CityTown,County,CountryID,BusinessTypeID,DirectorateFullDescription,PhoneNumber,Fax,Email,WebAddress,CharityNumber,CompanyNumber,ContactID,OrgID)
                                                 values(@DirectorateName,@DirectorateShortDescription,@AddressLine1,@AddressLine2,@AddressLine3,@AddressID,@CityTown,@County,@CountryID,@BusinessTypeID,@DirectorateFullDescription,@PhoneNumber,@Fax,@Email,@WebAddress,@CharityNumber,@CompanyNumber,@ContactID,@OrgID )";
           
                using (var helper=new SqlHelper())
                {
                    SqlParameter[]sqlParameters=new SqlParameter[19];
                    sqlParameters[0] = new SqlParameter("@DirectorateName", SqlDbType.NVarChar);
                    sqlParameters[0].Value = directorate.DirectorateName;
                    sqlParameters[1] = new SqlParameter("@DirectorateShortDescription", SqlDbType.NVarChar);
                    sqlParameters[1].Value = directorate.DirectorateShortDescription;
                    
                    sqlParameters[2] = new SqlParameter("@AddressLine1", SqlDbType.NVarChar);
                    sqlParameters[2].Value = directorate.AddressLine1;
                    sqlParameters[3] = new SqlParameter("@AddressLine2", SqlDbType.NVarChar);
                    sqlParameters[3].Value = directorate.AddressLine2;
                    sqlParameters[4] = new SqlParameter("@AddressLine3", SqlDbType.NVarChar);
                    sqlParameters[4].Value = directorate.AddressLine3;
                    sqlParameters[5] = new SqlParameter("@AddressID", SqlDbType.Int);
                    sqlParameters[5].Value = directorate.AddressId;
                    sqlParameters[6] = new SqlParameter("@CityTown", SqlDbType.NVarChar);
                    sqlParameters[6].Value = directorate.CityTown;
                    sqlParameters[7] = new SqlParameter("@County", SqlDbType.NVarChar);
                    sqlParameters[7].Value = directorate.County;
                    sqlParameters[8] = new SqlParameter("@CountryID", SqlDbType.Int);
                    sqlParameters[8].Value =directorate.CountryId;
                    sqlParameters[9] = new SqlParameter("@BusinessTypeID", SqlDbType.Int);
                    sqlParameters[9].Value = directorate.BusinessTypeId;
                    sqlParameters[10] = new SqlParameter("@DirectorateFullDescription", SqlDbType.NVarChar);
                    sqlParameters[10].Value = directorate.DirectorateFullDescription;
                    sqlParameters[11] = new SqlParameter("@PhoneNumber", SqlDbType.VarChar);
                    sqlParameters[11].Value = directorate.PhoneNumber;
                    sqlParameters[12] = new SqlParameter("@Fax", SqlDbType.NVarChar);
                    sqlParameters[12].Value = directorate.Fax;
                    sqlParameters[13] = new SqlParameter("@Email", SqlDbType.NVarChar);
                    sqlParameters[13].Value = directorate.Email;
                    sqlParameters[14] = new SqlParameter("@WebAddress", SqlDbType.NVarChar);
                    sqlParameters[14].Value = directorate.WebAddress;
                    sqlParameters[15] = new SqlParameter("@CharityNumber", SqlDbType.NVarChar);
                    sqlParameters[15].Value = directorate.CharityNumber;
                    sqlParameters[16] = new SqlParameter("@CompanyNumber", SqlDbType.NVarChar);
                    sqlParameters[16].Value = directorate.CompanyNumber;
                    sqlParameters[17] = new SqlParameter("@ContactID", SqlDbType.Int);
                    sqlParameters[17].Value = directorate.ContactId;
                    sqlParameters[18] = new SqlParameter("@OrgID",SqlDbType.Int);
                    sqlParameters[18].Value = directorate.OrgId;
                    helper.ExecuteInsertQuery(query, sqlParameters);
                }
                return true;
            }
            catch (Exception ex)
            {
                ////Log here
            }
            return retVal;

        }
        //method GetDirectorateInacTive
        public DataTable GetDirectorateInacTive(int idOrg)
        {
            string query = "select Directorate.DirectorateName,Directorate.AddressLine1,Directorate.IsActive,DirectorateID,PostCode,ContactName from Directorate inner join Address on Directorate.AddressID=Address.AddressID inner join Contact on Directorate.ContactID=Contact.ContactID where OrgID=@OrgID order by Directorate.DirectorateName asc";
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@OrgID", SqlDbType.Int);
            sqlParameters[0].Value = idOrg;
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }
        }
        //method FilterOrganizationByLetters
        public DataTable FilterDirectorateByLetters(string directorateName,int id,int flag)
        {
            string query =
                " select Directorate.DirectorateName,Directorate.AddressLine1,Directorate.IsActive,DirectorateID,PostCode,ContactName from Directorate inner join Address on Directorate.AddressID=Address.AddressID inner join Contact on Directorate.ContactID=Contact.ContactID ";
            if(flag==1)
            {
                query = query + "where ( Directorate.DirectorateName LIKE '['+@DirectorateName+']%') and  Directorate.OrgID=@OrgId order by  Directorate.DirectorateName asc";
            }
            else
            {
                query = query + "where ( Directorate.DirectorateName LIKE '['+@DirectorateName+']%') and  Directorate.OrgID=@OrgId and Directorate.IsActive =1 order by  Directorate.DirectorateName asc";
            }
            SqlParameter[] sqlParameters = new SqlParameter[2];
            sqlParameters[0] = new SqlParameter("@DirectorateName", SqlDbType.NVarChar);
            sqlParameters[0].Value = directorateName;
            sqlParameters[1] = new SqlParameter("@OrgId", SqlDbType.Int);
            sqlParameters[1].Value = id;
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }
        }
        //method GetDirectorateByDirectorareId
        public DataTable GetDirectorateByDirectorareId(int directorateId)
        {
            string query =
                "select DirectorateName,Directorate.DirectorateID,Organizations.OrgID,DirectorateShortDescription,Address.AddressID,BussinessType.BussinessTypeID,Contact.ContactID,Directorate.AddressLine1,Directorate.AddressLine2,Directorate.AddressLine3,Directorate.CityTown,Directorate.County,DirectorateFullDescription,Directorate.PhoneNumber,Directorate.Fax,Directorate.Email,Directorate.WebAddress,Directorate.CharityNumber,Directorate.CompanyNumber,ContactName,PostCode,BussinessType.BussinessTypeName,SICCode,Country.CountryID,Country.CountryName from" +
                " Directorate inner join [Address] on Directorate.AddressID=Address.AddressID inner join Contact on Directorate.ContactID=Contact.ContactID inner join BussinessType on Directorate.BusinessTypeID=  BussinessType.BussinessTypeID inner join Country on Directorate.CountryID=Country.CountryID inner join Organizations on Directorate.OrgID=Organizations.OrgID where DirectorateID=@direcId";
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@direcId", SqlDbType.Int);
            sqlParameters[0].Value = directorateId;
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }

        }
        //method UpdateDirectorate()
        public bool UpdateDirectorate(Directorate directorate)
        {
            bool retVal = false;
            try
            {
                string query = @"update  Directorate set DirectorateName=@DirectorateName,DirectorateShortDescription=@DirectorateShortDescription,AddressLine1= @AddressLine1,AddressLine2=@AddressLine2,AddressLine3=@AddressLine3,AddressID=@AddressID,
                                CityTown=@CityTown,County=@County,CountryID=@CountryID,BusinessTypeID=@BusinessTypeID,DirectorateFullDescription=@DirectorateFullDescription,PhoneNumber=@PhoneNumber,Fax=@Fax,Email=@Email,WebAddress=@WebAddress,CharityNumber=@CharityNumber,CompanyNumber=@CompanyNumber,ContactID=@ContactID,OrgID=@OrgID where Directorate.DirectorateID=@directorateId";
                                                                                                                                                                                                                                                                                                                                                                                                                                 

                using (var helper = new SqlHelper())
                {
                    SqlParameter[] sqlParameters = new SqlParameter[20];
                    sqlParameters[0] = new SqlParameter("@DirectorateName", SqlDbType.NVarChar);
                    sqlParameters[0].Value = directorate.DirectorateName;
                    sqlParameters[1] = new SqlParameter("@DirectorateShortDescription", SqlDbType.NVarChar);
                    sqlParameters[1].Value = directorate.DirectorateShortDescription;

                    sqlParameters[2] = new SqlParameter("@AddressLine1", SqlDbType.NVarChar);
                    sqlParameters[2].Value = directorate.AddressLine1;
                    sqlParameters[3] = new SqlParameter("@AddressLine2", SqlDbType.NVarChar);
                    sqlParameters[3].Value = directorate.AddressLine2;
                    sqlParameters[4] = new SqlParameter("@AddressLine3", SqlDbType.NVarChar);
                    sqlParameters[4].Value = directorate.AddressLine3;
                    sqlParameters[5] = new SqlParameter("@AddressID", SqlDbType.Int);
                    sqlParameters[5].Value = directorate.AddressId;
                    sqlParameters[6] = new SqlParameter("@CityTown", SqlDbType.NVarChar);
                    sqlParameters[6].Value = directorate.CityTown;
                    sqlParameters[7] = new SqlParameter("@County", SqlDbType.NVarChar);
                    sqlParameters[7].Value = directorate.County;
                    sqlParameters[8] = new SqlParameter("@CountryID", SqlDbType.Int);
                    sqlParameters[8].Value = directorate.CountryId;
                    sqlParameters[9] = new SqlParameter("@BusinessTypeID", SqlDbType.Int);
                    sqlParameters[9].Value = directorate.BusinessTypeId;
                    sqlParameters[10] = new SqlParameter("@DirectorateFullDescription", SqlDbType.NVarChar);
                    sqlParameters[10].Value = directorate.DirectorateFullDescription;
                    sqlParameters[11] = new SqlParameter("@PhoneNumber", SqlDbType.VarChar);
                    sqlParameters[11].Value = directorate.PhoneNumber;
                    sqlParameters[12] = new SqlParameter("@Fax", SqlDbType.NVarChar);
                    sqlParameters[12].Value = directorate.Fax;
                    sqlParameters[13] = new SqlParameter("@Email", SqlDbType.NVarChar);
                    sqlParameters[13].Value = directorate.Email;
                    sqlParameters[14] = new SqlParameter("@WebAddress", SqlDbType.NVarChar);
                    sqlParameters[14].Value = directorate.WebAddress;
                    sqlParameters[15] = new SqlParameter("@CharityNumber", SqlDbType.NVarChar);
                    sqlParameters[15].Value = directorate.CharityNumber;
                    sqlParameters[16] = new SqlParameter("@CompanyNumber", SqlDbType.NVarChar);
                    sqlParameters[16].Value = directorate.CompanyNumber;
                    sqlParameters[17] = new SqlParameter("@ContactID", SqlDbType.Int);
                    sqlParameters[17].Value = directorate.ContactId;
                    sqlParameters[18] = new SqlParameter("@OrgID", SqlDbType.Int);
                    sqlParameters[18].Value = directorate.OrgId;
                    sqlParameters[19]=new SqlParameter("@directorateId",SqlDbType.Int);
                    sqlParameters[19].Value = directorate.DirectorateId;
                    helper.ExecuteUpdateQuery(query, sqlParameters);
                }
                return true;
            }
            catch (Exception ex)
            {
                ////Log here
            }
            return retVal;
        }
        //method SetActiveDirectorate()
        public bool SetActiveDirectorate(int directorateId, bool status)
        {
            bool reply;
            string query = @"update Directorate set IsActive=@IsActive where Directorate.DirectorateID=@DrId ";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[2];
                sqlParameters[0] = new SqlParameter("@DrId", SqlDbType.Int);
                sqlParameters[0].Value = directorateId;
                sqlParameters[1] = new SqlParameter("@IsActive", SqlDbType.Bit);
                sqlParameters[1].Value = status;
                reply = helper.ExecuteUpdateQuery(query, sqlParameters);
            }
            return reply;
        }
        //SetDirectorateInActive()
        public bool SetDirectorateInActive(int directorateId, bool status)
        {
            bool reply;
            string query = @"update Directorate set IsActive=@IsActive where Directorate.DirectorateID=@DrId ";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[2];
                sqlParameters[0] = new SqlParameter("@DrId", SqlDbType.Int);
                sqlParameters[0].Value = directorateId;
                sqlParameters[1] = new SqlParameter("@IsActive", SqlDbType.Bit);
                sqlParameters[1].Value = status;
                reply = helper.ExecuteUpdateQuery(query, sqlParameters);
            }
            return reply;
        }
        public DataTable FilterOrganizationByLetters(string directorateName)
        {
            string query =
                " select Directorate.DirectorateName,Directorate.AddressLine1,Directorate.IsActive,DirectorateID,PostCode,ContactName from Directorate inner join Address on Directorate.AddressID=Address.AddressID inner join Contact on Directorate.ContactID=Contact.ContactID where ( Directorate.DirectorateName LIKE '['+@DirectorateName+']%') order by  Directorate.DirectorateName asc";
            SqlParameter[] sqlParameters = new SqlParameter[1];
            sqlParameters[0] = new SqlParameter("@DirectorateName", SqlDbType.NVarChar);
            sqlParameters[0].Value = directorateName;
            using (var helper = new SqlHelper())
            {
                return helper.ExecuteSelectQuery(query, sqlParameters);
            }
        }
        public int CheckDirectorateNameExist(string dirName)
        {
            string query = "select count(DirectorateName) from Directorate where DirectorateName=@DrName";
            using (var helper = new SqlHelper())
            {
                SqlParameter[] sqlParameters = new SqlParameter[1];
                sqlParameters[0] = new SqlParameter("@DrName", SqlDbType.NVarChar);
                sqlParameters[0].Value =dirName;
                return helper.ExecuteScalar(query, sqlParameters);
            }
        }
    }
}